package com.arethusa;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.*;

import javax.swing.JOptionPane;

import simpleorm.core.*;

public class Helper {

	static boolean isNumber( String s ) {
		
		StringCharacterIterator i = new StringCharacterIterator(s);
		char c;
		
		for(c=i.first(); c!=StringCharacterIterator.DONE; c=i.next() ) {
			if( ! Character.isDigit(c) ) return false; 
		}
		return true;
	}
	
	static void BepaalStatistieken() {
		BepaalNrStarts();
		BepaalNrNg();
		BepaalNrZiek();
		BepaalNrNa();
		BepaalNrLeencaps();
		BepaalNrPrijzen();
	}

	static void BepaalNrStarts() {
		boolean hasBegun = SConnection.getConnection().hasBegun();
		if( ! hasBegun ) { SConnection.begin(); }
		Connection JDBCcon = SConnection.getBegunDBConnection();
		try {
			Statement stmt = JDBCcon.createStatement();
			ResultSet res = stmt.executeQuery("SELECT ProgNr, Suffix, Count(*) as nr FROM Start" +
					"  GROUP BY ProgNr, Suffix");
			while( res.next() ) {
				Statement ustmt = JDBCcon.createStatement();
				ustmt.executeUpdate("UPDATE statistiekenperprogramma SET NrStarts="+ res.getInt("nr") +
						" WHERE ProgNr=" + res.getInt("ProgNr") + " AND Suffix='" + res.getString("Suffix") + "'");
			}
		} catch (SQLException e) {
			JOptionPane.showMessageDialog(null,
					e.getMessage()+e.getCause(),
					"SQL fout", JOptionPane.ERROR_MESSAGE);
		}
		if( ! hasBegun ) { SConnection.commit(); }
	}
	
	static void BepaalNrNg() {
		boolean hasBegun = SConnection.getConnection().hasBegun();
		if( ! hasBegun ) { SConnection.begin(); }
		Connection JDBCcon = SConnection.getBegunDBConnection();
		try {
			Statement stmt2 = JDBCcon.createStatement();
			ResultSet res2 = stmt2.executeQuery("SELECT ProgNr, Suffix, Count(*) as nr FROM Start" +
					" WHERE Eindtijd='agzk' GROUP BY Prognr, Suffix");
			while( res2.next() ) {
				Statement ustmt2 = JDBCcon.createStatement();
				ustmt2.executeUpdate("UPDATE statistiekenperprogramma SET NrNietGestart="+ res2.getInt("nr") +
						" WHERE ProgNr=" + res2.getInt("ProgNr") + " AND Suffix='" + res2.getString("Suffix") + "'");
			}
		} catch (SQLException e) {
			JOptionPane.showMessageDialog(null,
					e.getMessage()+e.getCause(),
					"SQL fout", JOptionPane.ERROR_MESSAGE);
		}
		if( ! hasBegun ) { SConnection.commit(); }
	}
	
	static void BepaalNrZiek() {
		boolean hasBegun = SConnection.getConnection().hasBegun();
		if( ! hasBegun ) { SConnection.begin(); }
		Connection JDBCcon = SConnection.getBegunDBConnection();
		try {
			Statement stmt3 = JDBCcon.createStatement();
			ResultSet res3 = stmt3.executeQuery("SELECT ProgNr, Suffix, Count(*) as nr FROM Start" +
					" WHERE Eindtijd='agmk' GROUP BY ProgNr, Suffix");
			while( res3.next() ) {
				Statement ustmt3 = JDBCcon.createStatement();
				ustmt3.executeUpdate("UPDATE statistiekenperprogramma SET NrZiek="+ res3.getInt("nr") +
						" WHERE ProgNr=" + res3.getInt("ProgNr") + " AND Suffix='" + res3.getString("Suffix") + "'");
			}
		} catch (SQLException e) {
			JOptionPane.showMessageDialog(null,
					e.getMessage()+e.getCause(),
					"SQL fout", JOptionPane.ERROR_MESSAGE);
		}
		if( ! hasBegun ) { SConnection.commit(); }
	}
	
	static void BepaalNrNa() {
		boolean hasBegun = SConnection.getConnection().hasBegun();
		if( ! hasBegun ) { SConnection.begin(); }
		Connection JDBCcon = SConnection.getBegunDBConnection();
		try {
			Statement stmt1 = JDBCcon.createStatement();
			ResultSet res1 = stmt1.executeQuery("SELECT ProgNr, Suffix, Count(*) as nr FROM Start" +
					" WHERE Eindtijd='na' GROUP BY ProgNr, Suffix");
			while( res1.next() ) {
				Statement ustmt1 = JDBCcon.createStatement();
				ustmt1.executeUpdate("UPDATE statistiekenperprogramma SET NrNietAangekomen="+ res1.getInt("nr") +
						" WHERE ProgNr=" + res1.getInt("ProgNr") + " AND Suffix='" + res1.getString("Suffix") + "'");
			}
		} catch (SQLException e) {
			JOptionPane.showMessageDialog(null,
					e.getMessage()+e.getCause(),
					"SQL fout", JOptionPane.ERROR_MESSAGE);
		}
		if( ! hasBegun ) { SConnection.commit(); }
	}
	
	static void BepaalNrLeencaps() {
		boolean hasBegun = SConnection.getConnection().hasBegun();
		if( ! hasBegun ) { SConnection.begin(); }
		Connection JDBCcon = SConnection.getBegunDBConnection();
		try {
			Statement stmt4 = JDBCcon.createStatement();
			ResultSet res4 = stmt4.executeQuery("SELECT ProgNr, Suffix, Count(*) as nr FROM Start" +
					" WHERE Vast=0 GROUP BY ProgNr, Suffix");
			while( res4.next() ) {
				Statement ustmt4 = JDBCcon.createStatement();
				ustmt4.executeUpdate("UPDATE statistiekenperprogramma SET NrVasteCap="+ res4.getInt("nr") +
						" WHERE ProgNr=" + res4.getInt("ProgNr") + " AND Suffix='" + res4.getString("Suffix") + "'");
			}
		} catch (SQLException e) {
			JOptionPane.showMessageDialog(null,
					e.getMessage()+e.getCause(),
					"SQL fout", JOptionPane.ERROR_MESSAGE);
		}
		if( ! hasBegun ) { SConnection.commit(); }
	}
	
	static void BepaalNrPrijzen() {
		boolean hasBegun = SConnection.getConnection().hasBegun();
		if( ! hasBegun ) { SConnection.begin(); }
		Connection JDBCcon = SConnection.getBegunDBConnection();
		try {
			Statement stmt = JDBCcon.createStatement();
			ResultSet res = stmt.executeQuery("SELECT ProgNr, Suffix, Count(*) as nr FROM Start" +
					"  GROUP BY ProgNr, Suffix");
			while( res.next() ) {
				Statement ustmt = JDBCcon.createStatement();
				ustmt.executeUpdate("UPDATE statistiekenperprogramma SET NrPrijzen="+ getNrPrijzen(new Integer(res.getInt("nr"))) +
						" WHERE ProgNr=" + res.getInt("ProgNr") + " AND Suffix='" + res.getString("Suffix") + "'");
			}
		} catch (SQLException e) {
			JOptionPane.showMessageDialog(null,
					e.getMessage()+e.getCause(),
					"SQL fout", JOptionPane.ERROR_MESSAGE);
		}
		if( ! hasBegun ) { SConnection.commit(); }
	}
	
	static Integer getNrStarts(Integer prognr, String suffix) {
		Integer nr = new Integer(0);
		boolean hasBegun = SConnection.getConnection().hasBegun();
		if( ! hasBegun ) { SConnection.begin(); }
		Connection JDBCcon = SConnection.getBegunDBConnection();
		try {
			if( prognr.intValue() == 0 || prognr == null ) {
				// bepaal totaal aantal starts
					Statement stmt = JDBCcon.createStatement();
					ResultSet res = stmt.executeQuery("SELECT Sum(NrStarts) as nr FROM statistiekenperprogramma");
					res.next();
					nr = new Integer(res.getInt("nr"));
			} else if( suffix == null || suffix.length() == 0 ) {
				// bepaal totaal voor hele programma
				Statement stmt1 = JDBCcon.createStatement();
				ResultSet res1 = stmt1.executeQuery("SELECT Sum(NrStarts) as nr FROM statistiekenperprogramma" +
						" WHERE ProgNr="+prognr);
				res1.next();
				nr = new Integer(res1.getInt("nr"));
			} else {
				// bepaal totaal per programma+suffix
				Statement stmt2 = JDBCcon.createStatement();
				ResultSet res2 = stmt2.executeQuery("SELECT NrStarts as nr FROM statistiekenperprogramma" +
						" WHERE ProgNr="+prognr+" AND Suffix='"+suffix+"'");
				res2.next();
				nr = new Integer(res2.getInt("nr"));
				
			}
		} catch (SQLException e) {
			JOptionPane.showMessageDialog(null,
					e.getMessage()+e.getCause(),
					"SQL fout", JOptionPane.ERROR_MESSAGE);
		}
		if( ! hasBegun ) { SConnection.commit(); }
		return nr;
	}
	
	static Integer getNrLeencaps(Integer prognr, String suffix) {
		Integer nr = new Integer(0);
		boolean hasBegun = SConnection.getConnection().hasBegun();
		if( ! hasBegun ) { SConnection.begin(); }
		Connection JDBCcon = SConnection.getBegunDBConnection();
		try {
			if( prognr == new Integer(0) || prognr == null ) {
				// bepaal totaal aantal starts
					Statement stmt = JDBCcon.createStatement();
					ResultSet res = stmt.executeQuery("SELECT Sum(NrVasteCap) as nr FROM statistiekenperprogramma");
					res.next();
					nr = new Integer(res.getInt("nr"));
			} else if( suffix == null || suffix.length() == 0 ) {
				// bepaal totaal voor hele programma
				Statement stmt1 = JDBCcon.createStatement();
				ResultSet res1 = stmt1.executeQuery("SELECT Sum(NrVasteCap) as nr FROM statistiekenperprogramma" +
						" WHERE ProgNr="+prognr);
				res1.next();
				nr = new Integer(res1.getInt("nr"));
			} else {
				// bepaal totaal per programma+suffix
				Statement stmt2 = JDBCcon.createStatement();
				ResultSet res2 = stmt2.executeQuery("SELECT NrVasteCap as nr FROM statistiekenperprogramma" +
						" WHERE ProgNr="+prognr+" AND Suffix='"+suffix+"'");
				res2.next();
				nr = new Integer(res2.getInt("nr"));
				
			}
		} catch (SQLException e) {
			JOptionPane.showMessageDialog(null,
					e.getMessage()+e.getCause(),
					"SQL fout", JOptionPane.ERROR_MESSAGE);
		}
		if( ! hasBegun ) { SConnection.commit(); }
		return nr;
	}
	
	static Integer getNrPrijzen(Integer prognr, String suffix) {
		Integer ns = getNrStarts(prognr, suffix);
		return getNrPrijzen(ns);
	}
	
	static private Integer getNrPrijzen(Integer nrStarts) {
		Integer nr = new Integer(0);
		if( nrStarts.intValue() <= 2 ) nr = new Integer(1);
	    if( nrStarts.intValue() > 2 && nrStarts.intValue() <= 4 ) nr = new Integer(2);
	    if( nrStarts.intValue() > 4 && nrStarts.intValue() <= 39 ) nr = new Integer(3);
	    if( nrStarts.intValue() > 39 ) nr = new Integer((nrStarts.intValue() /10));
		return nr;
	
	}
}
